<?php
/*
	KEY
	
	SD	: Sick Doctor => Ijin Sakit
	
	
*/ 



function excel_hari_kerja_kalkulasi(){
	$header = array(
		'NIK'=>array(),  
		'Nama'=>array(),   
		'IN'=>array(),  
		'PP'=>array(), 
		'BL'=>array(), 
		'BP'=>array(), 
		'SD'=>array(), 
		'C'=>array(), 
	);
	
	$end=get_batas_tmt() ;
	$query = " SELECT a.karyawan_id, a.nik FROM karyawan a
				INNER JOIN kekaryawanan b ON b.karyawan_id = a.karyawan_id 
				WHERE b.tmb  <= '{$end}' 
				AND a.karyawan_id NOT IN (select karyawan_id FROM karyawan_disable)
				AND a.karyawan_id NOT IN (select karyawan_id FROM pemberhentian  WHERE tmt <= '{$end}') 	
			";	
	$result = my_query($query);
	while($ey = my_fetch_array($result)){
			 
			$karyawan = loaddata_karyawan($ey['karyawan_id']);
			if(empty($karyawan['gelar'])){
				$nama_karyawan=ucfirst($karyawan['nama']) ;
			}else{
				$nama_karyawan=trim(ucfirst($karyawan['nama'])).", ".$karyawan['gelar'] ;
			}

			$jabatan = my_get_data_by_id('jabatan' ,'jabatan_id' , ($karyawan['jabatan_id']<>'' ? $karyawan['jabatan_id'] : 0 ));
			
			 $row[] = array(
				'nik'=>  "'".sprintf('%07d',$ey['nik']),       
				'gd'=> $nama_karyawan ,       
				'in'=> total_masuk($ey['karyawan_id']  )  , 
				'pp'=>  total_masuk($ey['karyawan_id'] , 'PP' )  , 
				'bl'=>  total_masuk($ey['karyawan_id'] , 'BL')   , 
				'bp'=>  total_masuk($ey['karyawan_id'] , 'BP')   , 
				'sd'=>  total_masuk($ey['karyawan_id'] , 'SD')  , 
				'c'=>  total_masuk($ey['karyawan_id'] , 'C')  
			);
		}
	
	$datas = table_rows_excel($row); 
	return table_builder_excel($header , $datas ,4 ,false ); 	
}

function list_karyawan_hari_kerja(  ){
	my_set_code_js('
		function confirmRst(){
			var t = confirm(\'Yakin akan reset data ?\');
			if(t){
				location.href=\'index.php?com='.$_GET['com'].'&task=rst\';
			}
			return false;
		}
	');
	$header = array(
		'NIK'=>array('style'=>'text-align:center;border-bottom:2px solid;width:10%'),  
		'Nama'=>array('style'=>'text-align:left;border-bottom:2px solid;width:27%'),  
		'Jabatan'=>array('style'=>'text-align:left;border-bottom:2px solid;width:36%'),  
		'IN'=>array('style'=>'text-align:center;border-bottom:2px solid;width: 4%'),  
		'PP'=>array('style'=>'text-align:center;border-bottom:2px solid;width:4%'), 
		'BT'=>array('style'=>'text-align:center;border-bottom:2px solid;width:4%'), 
		'BL'=>array('style'=>'text-align:center;border-bottom:2px solid;width:4%'), 
		'BP'=>array('style'=>'text-align:center;border-bottom:2px solid;width:4%'), 
		'SD'=>array('style'=>'text-align:center;border-bottom:2px solid;width:4%'), 
		'C'=>array('style'=>'text-align:center;border-bottom:2px solid;width:4%'), 
	);
	$row = array(); 
	$paging ='';
	if( check_tmp_records() ){
	$end=get_batas_tmt() ;
	if(isset($_GET['key'])){
		$query = "
			SELECT a.karyawan_id FROM karyawan a 
			INNER JOIN kekaryawanan b ON b.karyawan_id = a.karyawan_id 
			INNER JOIN biodata c ON a.biodata_id = c.biodata_id 
			WHERE b.tmb  <= '{$end}' 
			AND a.karyawan_id NOT IN (select karyawan_id FROM karyawan_disable)
			AND a.karyawan_id NOT IN (select karyawan_id FROM pemberhentian  WHERE tmt <= '{$end}') 	
			AND c.nama LIKE '%{$_GET['key']}%' OR a.nik ='{$_GET['key']}'
		";
	}else{	
		$query = " SELECT a.karyawan_id FROM karyawan a
				INNER JOIN kekaryawanan b ON b.karyawan_id = a.karyawan_id 
				WHERE b.tmb  <= '{$end}' 
				AND a.karyawan_id NOT IN (select karyawan_id FROM karyawan_disable)
				AND a.karyawan_id NOT IN (select karyawan_id FROM pemberhentian  WHERE tmt <= '{$end}') 	
			";	
	}
	$result = my_query($query);
	$total_records = my_num_rows($result );
	$scroll_page =7;  
	$per_page =25;  
	$current_page = isset($_GET['page']) ? (int) $_GET['page'] : 1 ; 
	if($current_page < 1){
		$current_page = 1;
	}		  
	$pager_url  ="index.php?com={$_GET['com']}&task={$_GET['task']}&periode_id={$_GET['periode_id']}&field={$_GET['field']}&key={$_GET['key']}&page=";	 
	$inactive_page_tag = 'style="padding:4px;background-color:#BBBBBB"';  
	$previous_page_text = ' Mundur '; 
	$next_page_text = ' Maju ';  
	$first_page_text = ' Awal '; 
	$last_page_text = ' Akhir ';
	
	
	$kgPagerOBJ = new kgPager();
			$kgPagerOBJ->pager_set(
				$pager_url, 
				$total_records, 
				$scroll_page, 
				$per_page, 
				$current_page, 
				$inactive_page_tag, 
				$previous_page_text, 
				$next_page_text, 
				$first_page_text, 
				$last_page_text ,$pager_url_last); 
				
	$result = my_query($query ." LIMIT ".$kgPagerOBJ->start.", ".$kgPagerOBJ->per_page); 	
	
	$i = ($current_page  - 1 ) * 25; 
	
		while($ey = my_fetch_array($result)){
			 
			$karyawan = loaddata_karyawan($ey['karyawan_id']);
			if(empty($karyawan['gelar'])){
				$nama_karyawan=ucfirst($karyawan['nama']) ;
			}else{
				$nama_karyawan=trim(ucfirst($karyawan['nama'])).", ".$karyawan['gelar'] ;
			}

			$jabatan = my_get_data_by_id('jabatan' ,'jabatan_id' , ($karyawan['jabatan_id']<>'' ? $karyawan['jabatan_id'] : 0 ));
			
			 $row[] = array(
				'nik'=>position_text_align ($karyawan['nik'] ,'center'),    
				'gd'=> $nama_karyawan ,      
				'jabatan'=> $jabatan['nama_jabatan']   ,
				'in'=>position_text_align(  total_masuk($ey['karyawan_id']  )  ,'center'),
				'pp'=> position_text_align( total_masuk($ey['karyawan_id'] , 'PP' )  ,'center'),
				'bt'=> position_text_align( total_masuk($ey['karyawan_id'] , 'BT' )  ,'center'),
				'bl'=>position_text_align(  total_masuk($ey['karyawan_id'] , 'BL')   ,'center'),
				'bp'=> position_text_align( total_masuk($ey['karyawan_id'] , 'BP')   ,'center'),
				'sd'=> position_text_align( total_masuk($ey['karyawan_id'] , 'SD')  ,'center'),
				'c'=> position_text_align( total_masuk($ey['karyawan_id'] , 'C')   ,'center')
			);
			
		}
	$paging = $kgPagerOBJ ->showPaging();	
	} 
	$datas = table_rows($row);
	
//	$periode_gajis = my_get_data_by_id('pr_periode','periode_id',$periode_id); 	 
	$search = '<form method="GET"><input type="hidden" name="com" value="'.$_GET['com'].'" />
	<span style="font:10px verdana"><b>Pencarian cepat</b></span>  
	<br/><input type="text"  style="width:200px;" name="key" value="'.(isset($_GET['key']) ? $_GET['key']: "").'" />
	<input class="simple_search" type="submit" value=" CARI " /> </form>';

	$view ='<table width="90%" cellspacing="0" cellpadding="4"><tr>'; 
	$view .='<table width="100%"   style="border-collapse:collapse;border-color:white" cellspacing="0" cellpadding="2">';
	//$view .= form_field_display( '<span class="label_form"><b>'.date('m-Y' $periode_gajis['nama_komponen_gaji']).'</b></span>', "<b>Komponen Gaji</b>"    ); 
	
	//$button_import =   ($komponen_gajis['reset_tutup_bulan'] =='yes') ? '';
	$view .= '<tr><td><br/>'.$search .'</td><td valign="bottom"><div style="width:100%;text-align:right;">
	'.$button_import .' 
	<input type="button" value="Excel" onclick="javascript:location.href=\'index.php?com='.$_GET['com'].'&task=excel_report\'" class="main_button" /> 
	<!--input type="button" value="Reset" onclick="javascript:confirmRst()" class="main_button" /--> 
	<!--input type="button" value="Upload" onclick="javascript: jQuery.facebox({ ajax: \'index.php?com='.$_GET['com'].'&task=upload&id=0\' });" class="main_button" /--> 
	 </div></td></tr>'   ; 
			return  $view.   table_builder($header , $datas ,  9 , false ,$paging );	

}

function check_tmp_records(){
	$query = "SELECT * FROM temp_tmn_hari LIMIT 2";
	$result = my_query($query);
	if( my_num_rows($result) > 0 ){
		return true;
	}
	return false; 
}


function total_masuk($karyawan_id , $prefix = false){
	$query = "SELECT DISTINCT tgl_index FROM temp_tmn_hari WHERE karyawan_id = {$karyawan_id} ";
	
	if($prefix){
		$query .= " AND status_absen = '{$prefix}' ";
	}else{
		$query .= " AND 
		( 
			status_absen = 'N' OR
			status_absen = '1' OR
			status_absen = '2' OR
			status_absen = '3' 
		)";
	}
	$result = my_query($query);
	$record = my_num_rows($result);
	return $record;
}


function parse_baris_data($baris , $month_current_periode , $year_current_periode){
	$datas = explode("," , $baris);
	 
	$karyawan_id =check_karyawan_by_nik(sprintf('%07d',$datas[0]));
	
	if((int)$month_current_periode > 1){
		$year_last_periode 	= $year_current_periode;
		$month_last_periode = $month_current_periode - 1;
	}else{
		$month_last_periode = 12; 
		$year_last_periode 	= $year_current_periode - 1;
	}
	
	//PREV PERIODE
	for($i = 11; $i<= 31; $i++){
		$date_index = $year_last_periode .'-'. sprintf("%02d",$month_last_periode). '-'. sprintf("%02d", $i);
		$datef = sprintf("%02d", $i);
		if(! chdate($date_index) )continue;
		
		$hari_libur = is_hari_libur($date_index) ? 'Y' : 'T' ;		
		$dinserts = array(
			'tgl_index'		=>	my_type_data_str($datef ),
			'hari_libur'	=>	my_type_data_str($hari_libur),
			'karyawan_id'	=>	my_type_data_str($karyawan_id),
			'nik'			=> my_type_data_str(sprintf('%07d',$datas[0])),
			'status_absen'=>	my_type_data_str($datas[$i]),
		);
		my_insert_record('temp_tmn_hari' , $dinserts);
	}
	unset($i);
	//CURRENT_PERIODE
	for($i = 1; $i <= 10; $i++){
		$date_index = $year_current_periode .'-'. sprintf("%02d",$month_current_periode). '-'. sprintf("%02d", $i);
		$datef = sprintf("%02d", $i);
		if(! chdate($date_index) )continue;
		
		$hari_libur = is_hari_libur($date_index) ? 'Y' : 'T' ;		
		$dinserts = array(
			'tgl_index'		=>	my_type_data_str($datef),
			'hari_libur'	=>	my_type_data_str($hari_libur),
			'karyawan_id'	=>	my_type_data_str($karyawan_id),
			'nik'=> my_type_data_str(sprintf('%07d',$datas[0])),
			'status_absen'=>	my_type_data_str($datas[$i]),
		);
		my_insert_record('temp_tmn_hari' , $dinserts);
	}
	
	
}

function is_saturday_sunday($date){
	$dayname = date( "l" , strtotime($date) );
	if($dayname == "Sunday"){
		return true;
	}else
	if($dayname == "Saturday"){
		return true;
	}
	 
	return false;
}

function is_hari_libur($date){
	if( is_saturday_sunday($date)){
		return true;
	}
	
	$query = "SELECT hari_libur_id FROM a_hari_libur WHERE tanggal ='{$date}' ";
	$result = my_query($query);
	if( my_num_rows($result) > 0){
		return true;
	}
	
	return false;
}

function chdate($date) {
	

	list($year, $month , $day) = explode("-", $date);  
	return checkdate(  $month, $day, $year);
}

function open_and_read_file($files, $month , $year){
	my_query("TRUNCATE TABLE temp_tmn_hari");
	$contents = file_get_contents($files);
	$allbaris = explode("
", $contents);
$n=0;
	foreach($allbaris as $baris){
	 parse_baris_data($baris , $month , $year);
		$n++;
	} 
	return true; 
}




function upload_hari_kerja_form_page(){
	$view = form_header( "upload" , "upload"  );
	
	$file = array(
			'name'=>'file',
			'value'=>(isset($_POST['file'])? $_POST['file'] : $fields['file']),
			'id'=>'file' ,'size'=>'70',
			'type'=>'file'
	);
	$form_nominal = form_dynamic($file);
	$view .= form_field_display( $form_nominal , "CSV Hari Kerja" );
	
	$submit = array(
		'value' => ' Proses ',
		'name' => 'simpan', 
		'type'=>'submit','class'=>'main_button'
	);
	$form_submit= form_dynamic($submit); 
	
	$view .= form_field_display( $form_submit  , "&nbsp;" ,  "" );
	$view .= form_footer( );
	return $view;
}


function upload_form_page_submit(){
	$dest ='files/csv/file_temp_hari_'.date('Ymd_his').'.csv';
	$ori = $_FILES['file']['tmp_name'];
	$upload = move_uploaded_file($ori , $dest); 
	if($upload){
		$q = 'TRUNCATE TABLE temp_tmn_hari';
		my_query($q);
		$query_get_date = " SELECT  tanggal FROM pr_periode WHERE gaji='Active' LIMIT 1";
		$result_query_get_date = my_query($query_get_date);
		$row_query_get_date = my_fetch_array( $result_query_get_date );
		list($yyyy , $mm, $dd) = explode("-" , $row_query_get_date['tanggal']  );
		open_and_read_file($dest, '04' , '2012');
		return $dest ;
	}
	return false;
}


function upload_form_validate(){
	$errsubmit = false;
	$err = array();
	
	$exts = array('csv','xls');
	$ext = end(explode("." , strtolower( $_FILES['file']['name'] ) ) );
	if( ! is_file($_FILES['file']['tmp_name']) ){
		$errsubmit = true;
		$err[] = "Tidak ada file yang disertakan";
	}
	elseif( ! in_array($ext , $exts)  ){
		$errsubmit = true;
		$err[] = "File tidak dikenali";
	}	
	
	if( $errsubmit){
		return $err;
	}
	return $errsubmit;
}

function proses_stor_to_log_hari_kerja($periode_id){
	$end=get_batas_tmt() ;
	$query = " SELECT a.karyawan_id FROM karyawan a
				INNER JOIN kekaryawanan b ON b.karyawan_id = a.karyawan_id 
				WHERE b.tmb  <= '{$end}' 
				AND a.karyawan_id NOT IN (select karyawan_id FROM karyawan_disable)
				AND a.karyawan_id NOT IN (select karyawan_id FROM pemberhentian  WHERE tmt <= '{$end}') 	
			";	 
	$i =0;
	$result = my_query($query);
	while($ey = my_fetch_array($result)){
		$datas = array(
				'karyawan_id'=>my_type_data_int($ey['karyawan_id']),
				'periode_id'=>my_type_data_int($periode_id),
				'in'	=>my_type_data_str(total_masuk($ey['karyawan_id']  ) ),	 	 	 	 	 	 	 
				'pp'	=>my_type_data_str(total_masuk($ey['karyawan_id'] , 'PP'  ) ),	 	 	 	 	 	 	 
				'bt'	=>my_type_data_str(total_masuk($ey['karyawan_id'] , 'BT'  ) ),		 	 	 	 	 	 	 
				'bl'	=>my_type_data_str(total_masuk($ey['karyawan_id'] , 'BL'  ) ),		 	 	 	 	 	 	 
				'bp'	=>my_type_data_str(total_masuk($ey['karyawan_id'] , 'BP'  ) ),		 	 	 	 	 	 	 
				'sd'	=>my_type_data_str(total_masuk($ey['karyawan_id'] , 'SD'  ) ),		 	 	 	 	 	 	 
				'c'		=>my_type_data_str(total_masuk($ey['karyawan_id'] , 'C'  ) ),
			);
		  my_insert_record( 'tmn_hari_kerja' , $datas);  
		  $i++;  	
	} 
	return $i;
}


function repair_log(){
	$file_contents = '_pot_april.csv';
	$contents = file_get_contents($file_contents);
	$allbaris = explode("
", $contents);
$i=0;
foreach($allbaris as $baris){
	stor_each_record($baris);
$i++;}
return $i;
} 
function stor_each_record($baris){
	$data = explode("," , $baris );
	$karyawan_id =check_karyawan_by_nik( $data[0] );
	if($karyawan_id == 0)return false;
	$query = "UPDATE tmn_hari_kerja SET
		 save_hari_prev_um	= '{$data[1]}' ,
		save_lembur_prev_um	  = '{$data[2]}' ,
		rem_hari_next_um	= '{$data[3]}' ,
		rem_lembur_next_um= '{$data[4]}'
		WHERE karyawan_id = {$karyawan_id}";
		 
	my_query($query);	
}